import pandas as pd
import os
from typing import Optional
from src.file.utils import get_file_path
from src.contribution.calculator import calculate_contribution
from openpyxl.styles import PatternFill


def save_excel_with_contribution(new_file: str,
                                 contribution_data: pd.DataFrame) -> bool:
    """保存包含贡献度sheet的新Excel文件"""
    new_path = get_file_path(new_file)
    try:
        # 检查文件是否被其他程序占用
        if os.path.exists(new_path):
            try:
                # 尝试以写模式打开文件来检查是否被占用
                with open(new_path, 'a'):
                    pass
            except PermissionError:
                print(f"文件 '{new_path}' 正在被其他程序使用，请关闭该文件后重试")
                return False
            except Exception:
                pass  # 忽略其他错误，继续尝试写入

        # 只写入贡献度数据到新文件
        with pd.ExcelWriter(new_path, engine='openpyxl') as writer:
            # 只添加贡献度sheet，不复制原文件的sheet
            contribution_data.to_excel(writer, sheet_name='贡献度', index=False, header=False)

            # 获取工作表对象以进行格式调整
            worksheet = writer.sheets['贡献度']

            # 导入配置中的 SUBJECTS
            from config.config import SUBJECTS

            # 设置分数列格式为2位小数
            from openpyxl.styles import NamedStyle
            decimal_style = NamedStyle(name='decimal_style')
            decimal_style.number_format = '0.00'

            # 设置贡献度列格式为0位小数
            contrib_style = NamedStyle(name='contrib_style')
            contrib_style.number_format = '0'

            # 如果样式不存在则添加
            if decimal_style.name not in writer.book.named_styles:
                writer.book.add_named_style(decimal_style)
            if contrib_style.name not in writer.book.named_styles:
                writer.book.add_named_style(contrib_style)

            # 遍历所有列，识别分数列并设置格式
            for col_idx in range(1, worksheet.max_column + 1):  # 从第一列开始
                # 获取表头单元格
                header_cell = worksheet.cell(row=1, column=col_idx)
                if header_cell.value in ["成绩", "总分"]:
                    # 这是分数列，为整列设置2位小数格式
                    for row_idx in range(2, worksheet.max_row + 1):  # 从第二行开始（跳过表头）
                        data_cell = worksheet.cell(row=row_idx, column=col_idx)
                        if isinstance(data_cell.value, (int, float)) and not isinstance(data_cell.value, bool):
                            data_cell.style = decimal_style
                elif header_cell.value == "贡献度":
                    # 这是贡献度列，为整列设置0位小数格式
                    for row_idx in range(2, worksheet.max_row + 1):  # 从第二行开始（跳过表头）
                        data_cell = worksheet.cell(row=row_idx, column=col_idx)
                        if isinstance(data_cell.value, (int, float)) and not isinstance(data_cell.value, bool):
                            data_cell.style = contrib_style
            
            # 应用新的格式化函数
            format_contribution_excel(worksheet)

        return True
    except PermissionError:
        print(f"没有权限保存文件 '{new_file}'，请检查文件是否被其他程序占用或者是否具有足够的权限")
        return False
    except Exception as e:
        print(f"保存Excel文件时出错: {e}")
        return False


def format_contribution_excel(worksheet):
    """
    格式化贡献度Excel表格
    将所有列都改为最小宽度，对贡献度列突出显示大于9和小于-9的值
    
    Args:
        worksheet: Excel工作表对象
    """
    # 设置所有列宽为适应内容
    from openpyxl.utils import get_column_letter
    for col_idx in range(1, worksheet.max_column + 1):
        column_letter = get_column_letter(col_idx)
        # 获取表头以确定列类型
        header_cell = worksheet.cell(row=1, column=col_idx)
        header_value = str(header_cell.value).strip() if header_cell.value is not None else ""
        
        # 计算列中内容的最大长度
        max_length = 0
        for row_idx in range(1, worksheet.max_row + 1):
            cell = worksheet.cell(row=row_idx, column=col_idx)
            if cell.value is not None:
                # 根据列类型计算显示宽度
                if header_value in ["成绩", "总分"] and isinstance(cell.value, (int, float)):
                    # 分数列显示为2位小数
                    cell_value = f"{cell.value:.2f}"
                elif header_value == "贡献度" and isinstance(cell.value, (int, float)):
                    # 贡献度列显示为整数
                    cell_value = f"{cell.value:.0f}"
                else:
                    # 其他列按实际内容显示
                    cell_value = str(cell.value)
                
                # 计算单元格内容的显示宽度，中文字符按2个单位宽度计算
                cell_length = 0
                for char in cell_value:
                    # 中文字符、标点符号等宽字符按2个单位计算
                    if ord(char) > 127:  # 非ASCII字符
                        cell_length += 2
                    else:  # ASCII字符按1个单位计算
                        cell_length += 1
                if cell_length > max_length:
                    max_length = cell_length
        # 设置列宽（增加一点边距）
        if max_length > 0:
            adjusted_width = min(max_length + 1, 50)  # 增加1个字符边距，最大宽度50
            worksheet.column_dimensions[column_letter].width = adjusted_width
    
    # 从配置中获取高亮阈值
    from config.config import CONTRIBUTION_CONFIG
    highlight_good = CONTRIBUTION_CONFIG['HIGHLIGHT']['GOOD']
    highlight_poor = CONTRIBUTION_CONFIG['HIGHLIGHT']['POOR']
    
    # 创建条件格式样式
    # 大于9的用绿色系颜色
    highlight_greater = PatternFill(start_color="C0FFC0", end_color="C0FFC0", fill_type="solid")  # 绿色
    # 小于-9的用红色系颜色
    highlight_less = PatternFill(start_color="FFC0C0", end_color="FFC0C0", fill_type="solid")     # 红色
    
    # 查找所有表头是"贡献度"的列
    contribution_columns = []
    for col_idx in range(1, worksheet.max_column + 1):
        header_cell = worksheet.cell(row=1, column=col_idx)
        # 处理可能的空白字符和数据类型
        header_value = str(header_cell.value).strip() if header_cell.value is not None else ""
        if header_value == "贡献度":
            contribution_columns.append(col_idx)
    
    # 应用条件格式到贡献度列
    for row_idx in range(2, worksheet.max_row + 1):  # 从第二行开始（跳过表头）
        for col_idx in contribution_columns:
            cell = worksheet.cell(row=row_idx, column=col_idx)
            if isinstance(cell.value, (int, float)) and not isinstance(cell.value, bool):
                if cell.value > highlight_good:
                    cell.fill = highlight_greater
                elif cell.value < highlight_poor:
                    cell.fill = highlight_less


def _save_contribution_file(filename: str, contribution_data: pd.DataFrame) -> bool:
    """保存贡献度文件"""
    new_filename = filename.replace('.xlsx', ' - 贡献度.xlsx')
    success = save_excel_with_contribution(new_filename, contribution_data)
    
    if success:
        print(f"成功生成文件: {new_filename}")
        return True
    else:
        print("保存文件失败")
        return False


def process_contribution_generation(filename: str) -> bool:
    """生成贡献度sheet并保存为新文件"""
    try:
        # 一次性读取文件数据
        from src.excel.reader import read_excel_file, get_class_count, get_subjects_info
        source_data = read_excel_file(filename)
        if source_data is None:
            return False

        # 自动计算班级数量
        num_classes: Optional[int] = get_class_count(source_data)
        if num_classes is None:
            print("无法获取班级数量")
            return False

        # 确认班级数量
        from src.file.utils import validate_and_update_class_count
        num_classes: Optional[int] = validate_and_update_class_count(num_classes)
        if num_classes is None:  # 输入格式有误
            return False

        # 自动生成考试科目列表
        auto_subjects_info: Optional[list[dict]] = get_subjects_info(source_data)
        if auto_subjects_info is not None:
            from src.file.utils import display_subjects_info, number_to_excel_column
            display_subjects_info(auto_subjects_info, number_to_excel_column)

        # 验证自动生成的科目列表
        from src.file.utils import get_user_confirmation
        if not get_user_confirmation("考试科目列表是否正确？（正确按回车，如错误请输入任意字符）"):
            print("考试科目列表不正确，请重新输入或检查源文件")
            return False

        # 计算贡献度数据
        contribution_data = calculate_contribution(source_data, num_classes, auto_subjects_info)
        if contribution_data.empty:
            print("贡献度数据计算失败")
            return False

        # 保存包含贡献度sheet的新文件
        return _save_contribution_file(filename, contribution_data)

    except Exception as e:
        print(f"处理贡献度数据时出错: {e}")
        return False